package com.william.jdbcplus.boot.test.dao.impl;

import com.william.jdbcplus.boot.starter.util.DaoUtil;
import com.william.jdbcplus.boot.starter.wrap.QueryLambdaBootWrap;
import com.william.jdbcplus.boot.test.dao.SysMenuDao;
import com.william.jdbcplus.boot.test.entity.*;
import com.william.jdbcplus.boot.test.vo.TreeVO;
import com.william.jdbcplus.core.toolkit.StringUtil;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;
import java.util.stream.Collectors;

@Repository
@AllArgsConstructor
@Slf4j
public class SysMenuDaoImpl implements SysMenuDao {
    /**
     * 通过父菜单ID查询子菜单
     *
     * @param parentId
     * @return
     */
    @Override
    public List<TreeVO> queryListByParentId(String parentId) {
        //        SELECT
        //                id
        //                ,parent_id
        //                ,name
        //                ,icon
        //                ,leaf_flag
        //        FROM   sys_permission
        //        WHERE 1=1
        //                <choose>
        //		   		<when test="parentId != null and parentId != ''">
        //                AND parent_id =  #{parentId,jdbcType=VARCHAR}
        //		   		</when>
        //		   		<otherwise>
        //                AND parent_id is null
        //                </otherwise>
        //		    </choose>

        QueryLambdaBootWrap bootstrap = new QueryLambdaBootWrap();
        bootstrap
                .selectAll(SysMenu.class)
                .from(SysMenu.class);
        if (StringUtil.isNotBlank(parentId)) {
            bootstrap.eq(SysMenu::getParentId, parentId);
        } else {
            bootstrap.isNull(SysMenu::getParentId);
        }

        List<SysMenu> list = bootstrap.queryList(SysMenu.class);
        List<TreeVO> result = list.stream().map(e -> new TreeVO(e)).collect(Collectors.toList());
        return result;
    }

    /**
     * 根据用户查询用户权限
     *
     * @param username
     */
    @Override
    public List<SysMenu> queryByUser(String username) {
        QueryLambdaBootWrap bootstrap = new QueryLambdaBootWrap();
        bootstrap
                .selectAll(SysMenu.class)
                .from(SysMenu.class)
                .eq(SysMenu::getDelFlag, 0)
                .and(e -> e
                        .exists(lb -> lb
                                .select(SysRoleMenu::getId)
                                .from(SysRoleMenu.class)
                                .innerJoin(SysRole.class, SysRole::getId, SysRoleMenu::getRoleId)
                                .innerJoin(SysUserRole.class, SysUserRole::getRoleId, SysRole::getId)
                                .innerJoin(SysUser.class, SysUser::getId, SysUserRole::getUserId)
                                .on(SysMenu::getId, SysRoleMenu::getPermissionId)
                                .eq(SysUser::getUsername, username)
                        )
                        .or(l -> l
                                .likeRight(SysMenu::getUrl, ":code")
                                .likeLeft(SysMenu::getUrl, "/online")
                                .eq(SysMenu::isHidden, true)
                        )
                        .or(l -> l.eq(SysMenu::getUrl, "/online"))
                );

        QueryLambdaBootWrap bootstrap1 = new QueryLambdaBootWrap();
        bootstrap1
                .selectAll(SysMenu.class)
                .from(SysMenu.class)
                .eq(SysMenu::getDelFlag, 0)
                .and(e -> e
                        .exists(lb -> lb
                                .select(SysDepartRoleMenu::getId)
                                .from(SysDepartRoleMenu.class)
                                .innerJoin(SysDepartRole.class, SysDepartRole::getId, SysDepartRoleMenu::getRoleId)
                                .innerJoin(SysDepartRoleUser.class, SysDepartRoleUser::getDroleId, SysDepartRole::getId)
                                .innerJoin(SysUser.class, SysUser::getId, SysDepartRoleUser::getUserId)
                                .on(SysMenu::getId, SysDepartRoleMenu::getPermissionId)
                                .eq(SysUser::getUsername, username)
                        )
                );

        //lambdaWrap.union(i -> lambdaWrap2);
        log.info("sql exec 1: " + bootstrap.getExecSql());

        List<SysMenu> list = bootstrap.queryList(SysMenu.class);
        List<SysMenu> list2 = bootstrap1.queryList(SysMenu.class);

        List<SysMenu> result = new ArrayList<>();
        result.addAll(list);
        result.addAll(list2);

        result = result.stream().sorted(Comparator.comparing(l -> l.getSortNo(), Comparator.nullsLast(Double::compareTo))).collect(Collectors.toList());
        return result;

        //        SELECT * FROM (
        //                SELECT p.*
        //                        FROM  sys_permission p
        //                WHERE (exists(
        //                        select a.id from sys_role_permission a
        //                        join sys_role b on a.role_id = b.id
        //                        join sys_user_role c on c.role_id = b.id
        //                        join sys_user d on d.id = c.user_id
        //                        where p.id = a.permission_id AND d.username = #{username,jdbcType=VARCHAR}
        //					)
        //        or (p.url like '%:code' and p.url like '/online%' and p.hidden = 1)
        //        or p.url = '/online')
        //        and p.del_flag = 0
        //                <!--update begin Author:lvdandan  Date:20200213 for：加入部门权限 -->
        //                UNION
        //        SELECT p.*
        //        FROM  sys_permission p
        //        WHERE exists(
        //                select a.id from sys_depart_role_permission a
        //        join sys_depart_role b on a.role_id = b.id
        //        join sys_depart_role_user c on c.drole_id = b.id
        //        join sys_user d on d.id = c.user_id
        //        where p.id = a.permission_id AND d.username = #{username,jdbcType=VARCHAR}
        //			   )
        //        and p.del_flag = 0
        //                <!--update end Author:lvdandan  Date:20200213 for：加入部门权限 -->
        //		   ) h order by h.sort_no ASC
    }

    /**
     * 根据用户查询用户权限
     *
     * @param username
     */
    public List<SysMenu> queryByUser2(String username) {
        QueryLambdaBootWrap bootstrap = new QueryLambdaBootWrap();
        bootstrap
                .selectAll(SysMenu.class)
                .from(SysMenu.class)
                .eq(SysMenu::getDelFlag, 0)
                .and(e -> e
                        .exists(lb -> lb
                                .select(SysRoleMenu::getId)
                                .from(SysRoleMenu.class)
                                .innerJoin(SysRole.class, SysRole::getId, SysRoleMenu::getRoleId)
                                .innerJoin(SysUserRole.class, SysUserRole::getRoleId, SysRole::getId)
                                .innerJoin(SysUser.class, SysUser::getId, SysUserRole::getUserId)
                                .on(SysMenu::getId, SysRoleMenu::getPermissionId)
                                .eq(SysUser::getUsername, username)
                        )
                        .or(l -> l
                                .likeRight(SysMenu::getUrl, ":code")
                                .likeLeft(SysMenu::getUrl, "/online")
                                .eq(SysMenu::isHidden, true)
                        )
                        .or(l -> l.eq(SysMenu::getUrl, "/online"))
                )
                .or(e -> e.
                        eq(SysMenu::isHidden, true)
                );
        log.info("sql exec 1: " + bootstrap.getExecSql());
        log.info("count 1: " + bootstrap.queryCount());
        bootstrap.clear();
        bootstrap
                .selectAll(SysMenu.class)
                .from(SysMenu.class)
                .eq(SysMenu::getDelFlag, 1)
                .orNest(a -> a
                                .exists(lb -> lb
                                        .select(SysRoleMenu::getId)
                                        .from(SysRoleMenu.class)
                                        .innerJoin(SysRole.class, SysRole::getId, SysRoleMenu::getRoleId)
                                        .innerJoin(SysUserRole.class, SysUserRole::getRoleId, SysRole::getId)
                                        .innerJoin(SysUser.class, SysUser::getId, SysUserRole::getUserId)
                                        .on(SysMenu::getId, SysRoleMenu::getPermissionId)
                                        .eq(SysUser::getUsername, username)
                                )
                        , l -> l
                                .likeRight(SysMenu::getUrl, ":code")
                                .likeLeft(SysMenu::getUrl, "/online")
                                .eq(SysMenu::isHidden, true)
                        , l -> l.eq(SysMenu::getUrl, "/online")
                )
                .or(e -> e.
                        eq(SysMenu::isHidden, true)
                );

        log.info("sql exec 2: " + bootstrap.getExecSql());
        log.info("count 2: " + bootstrap.queryCount());

        QueryLambdaBootWrap bootstrap1 = new QueryLambdaBootWrap();
        bootstrap1
                .selectAll(SysMenu.class)
                .from(SysMenu.class)
                .eq(SysMenu::getDelFlag, 0)
                .and(e -> e
                        .exists(lb -> lb
                                .select(SysDepartRoleMenu::getId)
                                .from(SysDepartRoleMenu.class)
                                .innerJoin(SysDepartRole.class, SysDepartRole::getId, SysDepartRoleMenu::getRoleId)
                                .innerJoin(SysDepartRoleUser.class, SysDepartRoleUser::getDroleId, SysDepartRole::getId)
                                .innerJoin(SysUser.class, SysUser::getId, SysDepartRoleUser::getUserId)
                                .on(SysMenu::getId, SysDepartRoleMenu::getPermissionId)
                                .eq(SysUser::getUsername, username)
                        )
                );

        //lambdaWrap.union(i -> lambdaWrap2);


        List<SysMenu> list = bootstrap.queryList(SysMenu.class);
        List<SysMenu> list2 = bootstrap1.queryList(SysMenu.class);

        List<SysMenu> result = new ArrayList<>();
        result.addAll(list);
        result.addAll(list2);

        result = result.stream().sorted(Comparator.comparing(l -> l.getSortNo(), Comparator.nullsLast(Double::compareTo))).collect(Collectors.toList());
        return result;

        //        SELECT * FROM (
        //                SELECT p.*
        //                        FROM  sys_permission p
        //                WHERE (exists(
        //                        select a.id from sys_role_permission a
        //                        join sys_role b on a.role_id = b.id
        //                        join sys_user_role c on c.role_id = b.id
        //                        join sys_user d on d.id = c.user_id
        //                        where p.id = a.permission_id AND d.username = #{username,jdbcType=VARCHAR}
        //					)
        //        or (p.url like '%:code' and p.url like '/online%' and p.hidden = 1)
        //        or p.url = '/online')
        //        and p.del_flag = 0
        //                <!--update begin Author:lvdandan  Date:20200213 for：加入部门权限 -->
        //                UNION
        //        SELECT p.*
        //        FROM  sys_permission p
        //        WHERE exists(
        //                select a.id from sys_depart_role_permission a
        //        join sys_depart_role b on a.role_id = b.id
        //        join sys_depart_role_user c on c.drole_id = b.id
        //        join sys_user d on d.id = c.user_id
        //        where p.id = a.permission_id AND d.username = #{username,jdbcType=VARCHAR}
        //			   )
        //        and p.del_flag = 0
        //                <!--update end Author:lvdandan  Date:20200213 for：加入部门权限 -->
        //		   ) h order by h.sort_no ASC
    }

    /**
     * 修改菜单状态字段： 是否子节点
     *
     * @param id
     * @param leaf
     */
    @Override
    public int setMenuLeaf(String id, int leaf) {
        // @Update("update sys_permission set is_leaf=#{leaf} where id = #{id}")
        SysMenu sysMenu = new SysMenu();
        sysMenu.setId(id);
        sysMenu.setLeaf(leaf > 0 ? true : false);
        DaoUtil.BASE.updateById(sysMenu);
        return 1;
    }

    /**
     * 获取模糊匹配规则的数据权限URL
     */
    @Override
    public List<String> queryPermissionUrlWithStar() {
        //  @Select("SELECT url FROM sys_permission WHERE del_flag = 0 and menu_type = 2 and url like '%*%'")
        QueryLambdaBootWrap bootstrap = new QueryLambdaBootWrap();
        bootstrap
                .select(SysMenu::getUrl)
                .from(SysMenu.class)
                .eq(SysMenu::getDelFlag, 0)
                .eq(SysMenu::getMenuType, 2)
                .like(SysMenu::getUrl, "*");

        List<String> list = bootstrap.queryList(String.class);
        return list;
    }

    /**
     * 根据用户账号查询菜单权限
     *
     * @param username
     * @param sysMenu
     * @return
     */
    @Override
    public int queryCountByUsername(String username, SysMenu sysMenu) {
        QueryLambdaBootWrap bootstrap = new QueryLambdaBootWrap();
        bootstrap
                .select("count(*) as cnt")
                .from(SysRoleMenu.class)
                .innerJoin(SysMenu.class, SysMenu::getId, SysRoleMenu::getPermissionId)
                .innerJoin(SysRole.class, SysRole::getId, SysRoleMenu::getRoleId)
                .innerJoin(SysUserRole.class, SysUserRole::getRoleId, SysRole::getId)
                .innerJoin(SysUser.class, SysUser::getId, SysUserRole::getUserId)
                .eq(SysUser::getUsername, username);

        if (StringUtil.isNotBlank(sysMenu.getId())) {
            bootstrap.eq(SysMenu::getId, sysMenu.getId());
        }
        if (StringUtil.isNotBlank(sysMenu.getUrl())) {
            bootstrap.eq(SysMenu::getUrl, sysMenu.getUrl());
        }

        QueryLambdaBootWrap bootstrap1 = new QueryLambdaBootWrap();
        bootstrap1
                .select("count(*) as cnt")
                .from(SysMenu.class)
                .innerJoin(SysDepartRoleMenu.class, SysDepartRoleMenu::getPermissionId, SysMenu::getId)
                .innerJoin(SysDepartRole.class, SysDepartRole::getId, SysDepartRoleMenu::getRoleId)
                .innerJoin(SysDepartRoleUser.class, SysDepartRoleUser::getDroleId, SysDepartRole::getId)
                .innerJoin(SysUser.class, SysUser::getId, SysDepartRoleUser::getUserId)
                .eq(SysUser::getUsername, username);

        if (StringUtil.isNotBlank(sysMenu.getId())) {
            bootstrap1.eq(SysMenu::getId, sysMenu.getId());
        }
        if (StringUtil.isNotBlank(sysMenu.getUrl())) {
            bootstrap1.eq(SysMenu::getUrl, sysMenu.getUrl());
        }

        int count1 = bootstrap.queryCount();
        int count2 = bootstrap1.queryCount();

        return count1 + count2;
    }
}
